package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.Role;
import edu.tms.bean.Student;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 学生dao
 * @author 任淋洋
 *
 */
public class StudentDaoImp implements CommnonDao<Student>{
	/**
	 * 保存内容
	 * 返回该条记录的自增长值（id）
	 * @param value
	 * @throws Exception
	 */
	@Override
	public long save(Student value) throws Exception {
		Student stu = (Student)value;
		String sql= "insert into t_student values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		return JdbcUtil.update(sql,stu.getStuId(),stu.getStuNo(),stu.getStuName(),stu.getStuAge()
			,stu.getStuSex(),stu.getStuClassName(),stu.getStuSquadId()
			,stu.getStuDormName(),stu.getStuNativePlace(),stu.getStuNation()
			,stu.getStuPoliticsStatus(),stu.getStuBirth(),stu.getStuPhone()
			,stu.getStuAddress(),stu.getStuQq(),stu.getStuEmail()
			,stu.getEnrollTime(),stu.getCreateTime(),stu.getStuState());
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Student value) throws Exception {
		Student stu = (Student)value;
		String sql= "update t_student set stu_id=?,stu_no=?,stu_name=?,stu_age=?,stu_sex=?,stu_class_name=?,stu_squad_id=?,"
		+"stu_dorm_name=?,stu_native_place=?,stu_nation=?,stu_politics_status=?,stu_birth=?,stu_phone=?,stu_address=?,"
		+"stu_qq=?,stu_email=?,enroll_time=?,create_time=?,stu_state=? where stu_id=?";
		return JdbcUtil.update(sql,stu.getStuId(),stu.getStuNo(),stu.getStuName(),stu.getStuAge(),stu.getStuSex(),stu.getStuClassName(),
			stu.getStuSquadId(),stu.getStuDormName(),stu.getStuNativePlace(),stu.getStuNation(),stu.getStuPoliticsStatus(),stu.getStuBirth(),
			stu.getStuPhone(),stu.getStuAddress(),stu.getStuQq(),stu.getStuEmail(),stu.getEnrollTime(),stu.getCreateTime(),
			stu.getStuState(),stu.getStuId());
	}
	/**
	 * 删除内容
	 * 相关联的表：
	 * 角色信息表t_character，学生信息表t_student，小组信息表t_squad
	 * 寝室表 t_dorm，学生考勤表t_attendance，成绩信息表t_grade
	 * 学生作业质量表t_student_work，信息记录表t_message
	 * 学生个人展示表t_school_life，学生测评记录表t_evaluating
	 * 入党申请表t_application_info，学生个人缴费情况表t_tuition，
	 * 审核表t_review， 登录日志表t_login_log，操作历史表t_operation
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Student value) throws Exception {
		Student stu = (Student)value;
		String sql ="delete t_student,t_attendance,t_grade,t_student_work,t_message,t_school_life,t_evaluating,t_application_info,t_tuition "
		+"from  t_student "
		+"left join t_attendance on t_student.stu_id = t_attendance.stu_id "
		+"left join t_grade on t_student.stu_id = t_grade.stu_id "
		+"left join t_student_work on t_student.stu_id = t_student_work.stu_id "
		+"left join t_message on t_student.stu_id = t_message.stu_id "
		+"left join t_school_life on t_student.stu_id = t_school_life.stu_id "
		+"left join t_evaluating on t_student.stu_id = t_evaluating.stu_id "
		+"left join t_application_info on t_student.stu_id = t_application_info.stu_id "
		+"left join t_tuition on t_student.stu_id = t_tuition.stu_id "
		+"where t_student.stu_id=?";
		return JdbcUtil.update(sql,stu.getStuId()); 
	}
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Student> findAll(int pageNo, int pageSize) throws Exception {
		String sql = "select * from t_student,t_squad where t_student.stu_squad_id=t_squad.squad_id limit ?,? ";
		List<Student> list=JdbcUtil.query(Student.class,sql,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	/**
	 * 查找单一实体
	 * @param key
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Student> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="select * from t_student "
		+"LEFT JOIN t_squad on t_student.stu_squad_id=t_squad.squad_id "
		+"LEFT JOIN t_class_info on t_student.stu_class_name=t_class_info.class_name "
		+"where t_student.stu_no=? or t_student.stu_name=? limit ?,?";
		return JdbcUtil.query(Student.class,sql,key,key,(pageNo-1)*pageSize,pageSize);
	}
	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	public Student findByKey(Object key) throws Exception {
		String sql="select * from t_student,t_squad,t_class_info "
				+"where t_student.stu_squad_id = t_squad.squad_id and t_student.stu_class_name = t_class_info.class_name "
				+"and t_student.stu_id = ? OR t_student.stu_no = ? ";
		return (Student) JdbcUtil.uniqueBean(Student.class,sql,key,key);
	}
	
	public Integer getClassId(Integer stu_id)throws Exception{
		String sql = "SELECT ci.class_id FROM t_class_info ci,t_student s WHERE ci.class_name = s.stu_class_name ";
		sql += "AND s.stu_id = ?";
		return (Integer) JdbcUtil.uniqueValue(sql, 1, stu_id);
	}
	
	public String getClassName(Integer stu_id)throws Exception{
		String sql = "SELECT stu_class_name FROM t_student WHERE stu_id = ?";
		return (String) JdbcUtil.uniqueValue(sql, 1, stu_id);
	}
	
	/**
	 * 计算党员申请通过的学生数量
	 * @param key 班级名称
	 * @param pageSize 每页行数
	 * @return 计算出来的页数
	 * @throws Exception
	 */
	public long getCountWithMember(Object key,int pageSize) throws Exception{
		String sql="select count(*) FROM  t_student INNER JOIN t_application_info ON t_student.stu_id=t_application_info.stu_id where stu_class_name=? && t_application_info.application_state=2";
		long count=(long) JdbcUtil.uniqueValue(sql, 1, key);
		return count%pageSize==0?count/pageSize:count/pageSize+1;
	}
	
	/**
	 * 虞艳
	 * 查询党员申请通过的学生和他的入党资料
	 * @param pageNo 当前页数
	 * @param pageSize 每页行数
	 * @return 查询到的学生
	 * @throws Exception  
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findAllApplication(Object key,int pageNo, int pageSize) throws Exception {
		String sql = "SELECT t_application_info.*,t_student.*  FROM  t_student ";
       sql+="INNER JOIN t_application_info ON t_student.stu_id=t_application_info.stu_id where stu_class_name=? and t_application_info.application_state= 2 limit ?,? ";
       return JdbcUtil.query(Student.class,sql,key,(pageNo-1)*pageSize,pageSize);
	}
	
	
	public Integer getSquadId(long stuId) throws Exception{
		String sql = "select stu_squad_id from t_student where stu_id = ?";
		return (Integer) JdbcUtil.uniqueValue(sql, 1, stuId);
	}
	
	/**
	 * 获取组长信息
	 */
	public Student getLeader(long stuId) throws Exception{
		String sql = "select stu_name,stu_phone,stu_qq from t_student where stu_id = ?";
		return (Student) JdbcUtil.uniqueBean(Student.class, sql, stuId);
	}
	
	/**
	 * 查询小组成员
	 * @param squadId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> getSquadMember(long squadId) throws Exception{
		String sql = "select stu_name,stu_Qq,stu_phone from t_student where stu_squad_id = ?";
		return JdbcUtil.query(Student.class, sql, squadId);
	}
	
	@SuppressWarnings("unchecked")
	public List<Student> findByTchId(Object key, int pageNo, int pageSize) throws Exception {
		String sql="select * from t_student "
				+ "left join t_class_info on t_student.stu_class_name=t_class_info.class_name "
				+ "where t_class_info.class_tch_id=? limit ?,?";
		return JdbcUtil.query(Student.class,sql,key,(pageNo-1)*pageSize,pageSize);
	}
	
	@SuppressWarnings("unchecked")
	public List<Student> findByCls(Object key, int pageNo, int pageSize) throws Exception {
		String sql="select * from t_student "
				+ "where stu_class_name=? limit ?,?";
		return JdbcUtil.query(Student.class,sql,key,(pageNo-1)*pageSize,pageSize);
	}
	
	public Student findById(Object key) throws Exception{
		String sql="select * from t_student where stu_no=?";
		return (Student)JdbcUtil.uniqueBean(Student.class,sql,key);
	}
	
	public long findClaCount(Object key) throws Exception{
		String sql="select count(*) from t_student where stu_class_name=?";
		long page=(long)JdbcUtil.uniqueValue(sql, 1, key);

		return page%10==0?page/10:page/10+1;
	}
	
	
	public long findByTchId(Object key) throws Exception {
		String sql="select count(*) from t_student "
				+ "left join t_class_info on t_student.stu_class_name=t_class_info.class_name "
				+ "where t_class_info.class_tch_id=?";
		long page=(long)JdbcUtil.uniqueValue(sql, 1, key);
		return page%10==0?page/10:page/10+1;
	}
	
	/**
	 * 根据班级名称查询班级成员
	 * @param classname
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findByClassName(String className,int pageNo, int pageSize) throws Exception {
		String sql = "SELECT sq.squad_name as stuSquadName,stu.* FROM t_student stu,t_squad sq "
				+ "WHERE stu.stu_squad_id = sq.squad_id "
				+ "AND stu.stu_class_name = ? ORDER BY sq.squad_name LIMIT ?,?";
		return JdbcUtil.query(Student.class,sql,className,(pageNo-1)*pageSize,pageSize);
	}
	
	public long getPagesClassName(String className, int pageSize) throws Exception {
		String sql = "SELECT count(*) FROM t_student stu,t_squad sq "
				+ "WHERE stu.stu_squad_id = sq.squad_id "
				+ "AND stu.stu_class_name = ? ";
		long counts = (long)JdbcUtil.uniqueValue(sql, 1, className);
		return counts%pageSize==0?counts/pageSize:counts/pageSize+1;
	}
	/**
	 * 根据班级名称及学生名字查询班级成员
	 * @param className
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findByClassName(String stuName, String className) throws Exception {
		String sql = "SELECT sq.squad_name as stuSquadName,stu.* FROM t_student stu,t_squad sq "
				+ "WHERE stu.stu_squad_id = sq.squad_id "
				+ "AND stu.stu_class_name = ?  AND stu.stu_name = ?";
		return JdbcUtil.query(Student.class,sql,className,stuName);
	}
	
	/**
	 * 根据班级名称及小组id查询班级成员
	 * @param className
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findByClassName(Integer squadId, String className) throws Exception {
		String sql = "SELECT sq.squad_name as stuSquadName,stu.* FROM t_student stu,t_squad sq "
				+ "WHERE stu.stu_squad_id = sq.squad_id "
				+ "AND stu.stu_class_name = ?  AND sq.squad_id = ?";
		return JdbcUtil.query(Student.class,sql,className,squadId);
	}
	
	public long pages(Object classname,int size) throws Exception{
		String sql="select count(*) from t_student where t_student.stu_class_name=?";
		long i=(long) JdbcUtil.uniqueValue(sql,1,classname);
		long pages=1;
		if(i%size==0)pages=i/size;
		else pages=i/size+1;
		return pages;
	}
	@SuppressWarnings("unchecked")
	public List<Student> findBustuidname(Object stuid,String stuname, int pageNo, int pageSize) throws Exception {
		String sql="select t_student.*,t_squad.squad_name as stu_squad_name from t_student,t_squad where t_student.stu_squad_id=t_squad.squad_id and t_student.stu_no=? "
		+"or stu_name=? limit ?,?";
		return JdbcUtil.query(Student.class,sql,stuid,stuname,(pageNo-1)*pageSize,pageSize);
	}
	@SuppressWarnings("unchecked")
	public List<Student> findByClassNameTeamId(Object classname,int pageNo, int pageSize) throws Exception {
		String sql="select * from t_student,t_squad where t_student.stu_squad_id=t_squad.squad_id and t_student.stu_squad_id=?  "
		+" limit ?,?";
		return JdbcUtil.query(Student.class,sql,classname,(pageNo-1)*pageSize,pageSize);
	}
	/**
	 * 根据班级ID查找该班级所有的班干部
	 * @param Key
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findAllOfClass(Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT * FROM t_character_role c,t_student stu WHERE c.char_id=stu.stu_id "
				+ "AND c.role_name!='学生' AND c.role_name!='超级管理员' AND c.role_name!='管理员' AND c.role_name!='辅导员' AND c.role_name!='专业教师'"
				+ " AND char_id IN( SELECT stu_id FROM t_student WHERE stu_class_name = ?) limit ?,?";
		List<Student> list = JdbcUtil.query(Student.class,sql,key,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	/**
	 * 根据班级ID查找该班级所有的班干部的总页数
	 * @param Key
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findAllOfClassInPage(Object key) throws Exception {
		String sql="SELECT * FROM t_character_role c,t_student stu WHERE c.char_id=stu.stu_id "
				+ "AND c.role_name!='学生' AND c.role_name!='超级管理员' AND c.role_name!='管理员' AND c.role_name!='辅导员' AND c.role_name!='专业教师'"
				+ " AND char_id IN( SELECT stu_id FROM t_student WHERE stu_class_name = ?)";
		List<Student> list = JdbcUtil.query(Student.class,sql,key);
		return list;
	}
	/**
	 * 根据班级ID查找该班级所有的学生
	 * @param Key
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findAllStuOfClass(Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT * FROM t_character_role c, t_student stu WHERE c.char_id=stu.stu_id "
				+ "AND stu.stu_class_name=? AND c.role_name='学生' limit ?,?";
		List<Student> list = JdbcUtil.query(Student.class,sql,key,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	/**
	 * 根据班级ID查找该班级所有的学生的总页数
	 * @param Key
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findAllStuOfClassInPage(Object key) throws Exception {
		String sql="SELECT * FROM t_character_role c, t_student stu WHERE c.char_id=stu.stu_id "
				+ "AND stu.stu_class_name=? AND c.role_name='学生'";
		List<Student> list = JdbcUtil.query(Student.class,sql,key);
		return list;
	}
	/**
	 * 根据班级ID和姓名查找单一学生
	 * @param findStuName 
	 * @param Key
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findStuOfClass(Object key, String findStuName) throws Exception {
		String sql="SELECT * FROM t_character_role c, t_student stu WHERE c.char_id=stu.stu_id "
				+ "AND stu.stu_class_name=? AND stu.stu_name=? AND c.role_name='学生'";
		List<Student> list = JdbcUtil.query(Student.class,sql,key,findStuName);
		return list;
	}
	/**
	 * 修改职位名称
	 * @param value
	 * @throws Exception
	 */
	public int updateRoleInStu(Object value) throws Exception {
		if(value instanceof Student){
			Student stu = (Student)value;
			String sql= "UPDATE t_character_role c,t_student stu SET c.role_name=? "
					+ "WHERE c.char_id=stu.stu_id AND stu_id=?";
			return JdbcUtil.update(sql,stu.getRoleName(),stu.getStuId());
		}
		return 0;
	}
	/**
	 * 查询某个职务名称的职务ID
	 */
	@SuppressWarnings("unchecked")
		public Integer findRoleId(Object key) throws Exception {
			String sql="SELECT role_id FROM t_role WHERE role_name=?";
			List<Role> list = JdbcUtil.query(Role.class,sql,key);
			int a = list.get(0).getRoleId();
			return a;
	}
	/**
	 * 查询某个职务ID的职务名称
	 */
	@SuppressWarnings("unchecked")
		public String findRoleName(Object key) throws Exception {
			String sql="SELECT role_name FROM t_role WHERE role_id=?";
			List<Role> list = JdbcUtil.query(Role.class,sql,key);
			String a = list.get(0).getRoleName();
			return a;
	}
}
